Skip Headers

Oracle® Database JDBC Developer's Guide and Reference 10g Release 2 (10.2) Part Number B14355-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# B Coding Tips

This appendix describes methods to optimize a Java Database Connectivity (JDBC) application or applet. It includes the following topics:

- JDBC and Multithreading

- Performance Optimization

- Transaction Isolation Levels and Access Modes

## JDBC and Multithreading

The Oracle JDBC drivers provide full support for, and are highly optimized for, applications that use Java multithreading. Controlled serial access to a connection, such as that provided by connection caching, is both necessary and encouraged. However, Oracle strongly discourages sharing a database connection among multiple threads. Avoid allowing multiple threads to access a connection simultaneously. If multiple threads must share a connection, use a disciplined begin-using/end-using protocol.

## Performance Optimization

You can significantly enhance the performance of your JDBC programs by using any of these features:

- Disabling Auto-Commit Mode

- Standard Fetch Size and Oracle Row Prefetching

- Standard and Oracle Update Batching

- Statement Caching

- Mapping Between Built-in SQL and Java Types

### Disabling Auto-Commit Mode

Auto-commit mode indicates to the database whether to issue an automatic `COMMIT` operation after every SQL operation. Being in auto-commit mode can be expensive in terms of time and processing effort if, for example, you are repeating the same statement with different bind variables.

By default, new connection objects are in auto-commit mode. However, you can disable auto-commit mode with the `setAutoCommit` method of the connection object, either `java.sql.Conection` or `oracle.jdbc.OracleConnection`.

In auto-commit mode, the `COMMIT` operation occurs either when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a `ResultSet`, the statement completes when the last row of the `ResultSet` has been retrieved or when the `ResultSet` has been closed. In more complex cases, a single statement can return multiple results as well as output parameter values. Here, the `COMMIT` occurs when all results and output parameter values have been retrieved.

If you disable auto-commit mode with a `setAutoCommit(false)` call, then you must manually commit or roll back groups of operations using the `commit` or `rollback` method of the connection object.

Example

The following example illustrates loading the driver and connecting to the database. Because new connections are in auto-commit mode by default, this example shows how to disable auto-commit. In the example, `conn` represents the `Connection` object, and `stmt` represents the `Statement` object.

    // Connect to the database
    // You can put a database hostname after the @ sign in the connection URL.
       OracleDataSource ods = new OracleDataSource();   ods.setURL("jdbc:oracle:oci:@");   ods.setUser("scott");   ods.setPassword("tiger");   Connection conn = ods.getConnection();
    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();
    ...

### Standard Fetch Size and Oracle Row Prefetching

Oracle JDBC connection and statement objects allow you to specify the number of rows to prefetch into the client with each trip to the database while a result set is being populated during a query. You can set a value in a connection object that affects each statement produced through that connection, and you can override that value in any particular statement object. The default value in a connection object is 10. Prefetching data into the client reduces the number of round trips to the server.

Similarly, and with more flexibility, JDBC 2.0 enables you to specify the number of rows to fetch with each trip, both for statement objects (affecting subsequent queries) and for result set objects (affecting row refetches). By default, a result set uses the value for the statement object that produced it. If you do not set the JDBC 2.0 fetch size, then the Oracle connection row-prefetch value is used by default.

See Also: "Oracle Row Prefetching" and "Fetch Size"

### Standard and Oracle Update Batching

The Oracle JDBC drivers allow you to accumulate `INSERT`, `DELETE`, and `UPDATE` operations of prepared statements at the client and send them to the server in batches. This feature reduces round trips to the server. You can either use Oracle update batching, which typically processes a batch implicitly once a pre-set batch value is reached, or standard update batching, where the batch is processed explicitly.

See Also: "Update Batching" When SQL data is retrieved into Java variables by JDBC programs, the SQL data is converted to the Java data types of those variables. This section describes the mapping from SQL built-in types to Java, then describes the mappings for Oracle 8 user-defined types: varray, nested table, Oracle Object and `REF` types.

### Statement Caching

Statement caching improves performance by caching executable statements that are used repeatedly, such as in a loop or in a method that is called repeatedly. Applications use the statement cache to cache statements associated with a particular physical connection. When you enable statement caching, a statement object is cached when you call the `close` method. Because each physical connection has its own cache, multiple caches can exist if you enable statement caching for multiple physical connections.

When you enable statement caching on a connection cache, the logical connections benefit from the statement caching that is enabled on the underlying physical connection. If you try to enable statement caching on a logical connection held by a connection cache, then this will throw an exception.

See Also: Chapter 22, "Statement Caching"

### Mapping Between Built-in SQL and Java Types

The SQL built-in types are those types with system-defined names, such as `NUMBER`, and `CHAR`, as opposed to the Oracle objects, varray, and nested table types, which have user-defined names. In JDBC programs that access data of built-in SQL types, all type conversions are unambiguous, because the program context determines the Java type to which a SQL datum will be converted.

Table B-1 is a subset of the information presented in Table 13-1. The table lists the one-to-one type-mapping of the SQL database type to its Java `oracle.sql.*` representation.

Table B-1 Mapping of SQL Data Types to Java Classes that Represent SQL Data Types

SQL Data Type | ORACLE Mapping - Java Classes Representing SQL Data Types
--- | ---
`CHAR` | `oracle.sql.CHAR`
`VARCHAR2` | `oracle.sql.CHAR`
`DATE` | `oracle.sql.DATE`
`DECIMAL` | `oracle.sql.NUMBER`
`DOUBLE PRECISION` | `oracle.sql.NUMBER`
`FLOAT` | `oracle.sql.NUMBER`
`INTEGER` | `oracle.sql.NUMBER`
`REAL` | `oracle.sql.NUMBER`
`RAW` | `oracle.sql.RAW`
`LONG RAW` | `oracle.sql.RAW`
`REF CURSOR` | `java.sql.ResultSet`
`CLOB LOCATOR` | `oracle.sql.CLOB`
`BLOB LOCATOR` | `oracle.sql.BLOB`
`BFILE` | `oracle.sql.BFILE`
nested table | `oracle.sql.ARRAY`
varray | `oracle.sql.ARRAY`
SQL object value | If there is no entry for the object value in the type map: `oracle.sql.STRUCT` If there is an entry for the object value in the type map: customized Java class
REF to SQL object type | class that implements `oracle.sql.SQLRef`, typically by extending `oracle.sql.REF`

This mapping provides the most efficient conversion between SQL and Java data representations. It stores the usual representations of SQL data as byte arrays. It avoids re-formatting the data. It is information-preserving. This Oracle mapping is the most efficient type-mapping for applications that "shovel" data from SQL to Java, or vice versa.

The most efficient way to access numeric data is as primitive Java types like `int`, `float`, `long`, and `double`. However, the range of values of these types do not exactly match the range of values of the SQL `NUMBER` data type. As a result, there may be some loss of information.

All character data is converted to the UCS2 character set of Java. The most efficient way to access character data is as `java.lang.String`. In worst case, this can cause a loss of information when two or more characters in the database character set map to a single UCS2 character. In Oracle Database 10g, all characters in the character set map to the characters in the UCS2 character set. However, some characters do map to surrogate pairs.

## Transaction Isolation Levels and Access Modes

Read-only connections are supported by the Oracle server, but not by the Oracle JDBC drivers.

For transactions, the Oracle server supports only the `TRANSACTION_READ_COMMITTED` and `TRANSACTION_SERIALIZABLE` transaction isolation levels. The default is `TRANSACTION_READ_COMMITTED`. Use the following methods of the `oracle.jdbc.OracleConnection` interface to get and set the level:

- `getTransactionIsolation`: Gets this connection's current transaction isolation level.

- `setTransactionIsolation`: Changes the transaction isolation level, using one of the `TRANSACTION_*` values.